import os
from config.configs import DESKTOP_PATH, GOAT_ERP_HOST, GOAT_ERP_PORT, DATA_PATH
from tools.funciton import write_xlsx, read_xlsx_file, skuId_to_size, spuId_to_articleNumber, timestamp_switch_date, \
    replace_column_values
import pymongo

korea = pymongo.MongoClient(GOAT_ERP_HOST, GOAT_ERP_PORT).goat_warehouse_korea


def korea_stock_deposit_export(cond, filename):
    """韩国仓库存导出"""
    datas = []
    res = korea.stockdeposits.find(cond)
    for i in res:
        if spuId_to_articleNumber('korea', i['spuId']) is not []:
            datas.append([i['appointNo'], i['uniqueCode'], spuId_to_articleNumber('korea', i['spuId']), skuId_to_size('korea',i['skuId']),
                          i['inStockQty'],
                          i['inStockQty'] - i['qty'] - i['waitOutStockQty'], i['waitOutStockQty'], i['qty'],
                          i['isFail'], i['shelfCode'], i['merchantName'], timestamp_switch_date(i['createdAt'])])
        else:
            datas.append([i['appointNo'], i['uniqueCode'], i['spuId'], i['skuId'], i['inStockQty'],
                          i['inStockQty'] - i['qty'] - i['waitOutStockQty'], i['waitOutStockQty'], i['qty'],
                          i['isFail'], i['shelfCode'], i['merchantName'], timestamp_switch_date(i['createdAt'])])
    write_xlsx(
        ['入库单号', '唯一码', '货号', '尺码', '入库数', '已出库数量', '待出库数量', '在仓库存数量', '类型', '货架号',
         '商家名称', '入库时间'], datas, filename)
    file = os.path.join(DATA_PATH, f'{filename}.xlsx')
    replace_column_values(file_path=file, sheet_name='sheet1', column_index=8, old_value=False, new_value='全新')
    replace_column_values(file_path=file, sheet_name='sheet1', column_index=8, old_value=True, new_value='瑕疵')


# korea_stock_deposit_export({'merchantCode': "00015"}, 'SLJ入库明细导出0113')


def korea_stock_out_export(cond, filename):
    """韩国仓出库记录导出"""
    pipeline = [
        {
            '$match': cond
        },
        {
            '$unwind': {
                'path': "$skuList"
            }
        },
        {
            '$lookup': {
                'from': "expresses",
                'localField': "outStockNo",
                'foreignField': "originNo",
                'as': "result"
            }
        },
        {
            '$unwind': {
                'path': "$result"
            }
        },
        {
            '$project': {
                '_id': 0,
                '出库单号': "$outStockNo",
                '商家编号': "$merchantCode",
                '商家名称': "$merchantName",
                '状态': "$status",
                '货号': "$skuList.spuId",
                '尺码': "$skuList.skuId",
                '瑕疵': "$skuList.isFail",
                '唯一码': "$skuList.uniqueCode",
                '出库总数': "$skuList.qty",
                '已捡货数量': "$skuList.outQty",
                '物流公司': "$result.type",
                '物流单号': "$expressNo",
                '创建时间': "$createdAt",
                '捡货时间': "$pickedTime",
                '发货时间': "$deliverTime",
                '取消时间': "$cancelTime"
            }
        }
    ]
    datas = []
    res = korea.stockoutorders.aggregate(pipeline=pipeline)
    for i in res:
        if '取消时间' in i:
            datas.append([i['出库单号'], i['商家编号'], i['商家名称'], i['状态'], spuId_to_articleNumber('korea', i['货号']),
                          skuId_to_size('korea',i['尺码']),
                          i['瑕疵'], i['唯一码'], i['出库总数'], '', '', '', timestamp_switch_date(i['创建时间']),
                          '', '', timestamp_switch_date(i['取消时间'])])
        else:
            datas.append([i['出库单号'], i['商家编号'], i['商家名称'], i['状态'], spuId_to_articleNumber('korea', i['货号']),
                          skuId_to_size('korea',i['尺码']),
                          i['瑕疵'], i['唯一码'], i['出库总数'], i['已捡货数量'], i['物流公司'], i['物流单号'],
                          timestamp_switch_date(i['创建时间']),
                          timestamp_switch_date(i['捡货时间']), timestamp_switch_date(i['发货时间'])])
    write_xlsx(['出库单号', '商家编号', '商家名称', '状态', '货号', '尺码', '瑕疵', '唯一码', '出库总数', '已捡货总数',
                '物流公司', '物流单号', '创建时间', '捡货时间', '发货时间', '取消时间'], datas, filename)
    file = os.path.join(DATA_PATH, f'{filename}.xlsx')
    replace_column_values(file_path=file, sheet_name='sheet1', column_index=6, old_value=False, new_value='全新')
    replace_column_values(file_path=file, sheet_name='sheet1', column_index=6, old_value=True, new_value='瑕疵')
    replace_column_values(file_path=file, sheet_name='sheet1', column_index=9, old_value='CJ', new_value='CJ物流')
    replace_column_values(file_path=file, sheet_name='sheet1', column_index=9, old_value='CD1', new_value='用户自送')
    replace_column_values(file_path=file, sheet_name='sheet1', column_index=9, old_value='JD', new_value='京东')
    replace_column_values(file_path=file, sheet_name='sheet1', column_index=9, old_value='SF', new_value='顺丰')
    replace_column_values(file_path=file, sheet_name='sheet1', column_index=9, old_value='DB', new_value='德邦')
    replace_column_values(file_path=file, sheet_name='sheet1', column_index=9, old_value='ZTO', new_value='中通')
    replace_column_values(file_path=file, sheet_name='sheet1', column_index=9, old_value='STO', new_value='申通')
    replace_column_values(file_path=file, sheet_name='sheet1', column_index=9, old_value='YD', new_value='韵达')
    replace_column_values(file_path=file, sheet_name='sheet1', column_index=9, old_value='EMS', new_value='EMS')
    replace_column_values(file_path=file, sheet_name='sheet1', column_index=9, old_value='KYE', new_value='跨越速运')
    replace_column_values(file_path=file, sheet_name='sheet1', column_index=9, old_value='YTO', new_value='圆通')


korea_stock_out_export({'merchantCode':"00015"}, 'SLG出库明细导出0113')


def in_stock_appointment_export(cond, name):
    """韩国仓入仓记录导出"""
    li = []
    pipeline = [
        {
            '$match':
                cond
        },
        {
            '$project':
                {
                    'merchantName': 1,
                    'spus': {'$objectToArray': "$spus"},
                    'createdAt': 1,
                    'expressNo': "$appointNo"
                }
        }, {
            '$unwind': {
                'path': "$spus",
            }
        }, {
            '$project': {
                'spuid': "$spus.k",
                'skudata': {'$objectToArray': "$spus.v"},
                'merchantName': 1,
                'expressNo': 1,
                'createdAt': 1
            }
        }, {
            '$unwind': {
                'path': "$skudata",
            }
        }, {
            '$project': {
                'sku': "$skudata.k",
                'spu': "$spuid",
                'expressNo': 1,
                'merchantName': 1,
                'createdAt': 1,
                'num': "$skudata.v"
            }
        }, {
            '$project': {
                'merchantName': 1,
                'expressNo': 1,
                'spu': 1,
                'sku': 1,
                'createdAt': 1,
                'num': "$num.appointNum"
            }
        }
    ]
    for i in korea.appointments.aggregate(pipeline):
        li.append([i['expressNo'], i['merchantName'], timestamp_switch_date(i['createdAt']),
                   spuId_to_articleNumber('korea', int(i['spu'])), skuId_to_size('korea', int(i['sku'])), i['num']])
    write_xlsx(['物流单号', '商家名称', '创建时间', '货号', '尺码', '数量'], li, name=name)


in_stock_appointment_export({"merchantCode": "00015", "status": 9},'00015预约明细')
